﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using OgilvyOne.Model;
using OgilvyOne.IDAL;
using OgilvyOne.Utility;

namespace OgilvyOne.SqlServerDAL
{
    public class CmsArticleVideo : ICmsArticleVideo
    {

        private const string ADD_SQL = "insert into CMS_ARTICLE_VIDEO(VIDEO_NAME,VIDEO_FILE_NAME,VIDEO_TYPE,VIDEO_DESC,VIDEO_ORDER,ARTICLE_ID,STATE,ADD_TIME,USER_ID) values(@VIDEO_NAME,@VIDEO_FILE_NAME,@VIDEO_TYPE,@VIDEO_DESC,@VIDEO_ORDER,@ARTICLE_ID,@STATE,getDate(),@USER_ID);select @@IDENTITY";
        //添加
        public int Add(CmsArticleVideoModel model)
        {

            SqlParameter[] parms = new SqlParameter[] { 
                new SqlParameter("@VIDEO_NAME", SqlDbType.VarChar,255),
                new SqlParameter("@VIDEO_TYPE",SqlDbType.Int,4),
                new SqlParameter("@VIDEO_FILE_NAME", SqlDbType.VarChar,50),
                new SqlParameter("@VIDEO_DESC", SqlDbType.VarChar,2000),
                new SqlParameter("@VIDEO_ORDER",SqlDbType.Int,4),
                new SqlParameter("@ARTICLE_ID",SqlDbType.Int,4),
                new SqlParameter("@STATE",SqlDbType.Int,4),
                new SqlParameter("@USER_ID",SqlDbType.Int,4)
            };
            parms[0].Value = model.VideoName;
            parms[1].Value = model.videoType;
            parms[2].Value = model.VideoFileName;
            parms[3].Value = model.videoDesc;
            parms[4].Value = model.VideoOrder;
            parms[5].Value = model.ArticleId;
            parms[6].Value = model.State;
            parms[7].Value = model.UserId;
            

            int id = 0;
            using (SqlConnection conn = new SqlConnection(SQLHelper.CONN_STRING_NON_DTC))
            {
                conn.Open();
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    try
                    {
                        string s = SQLHelper.ExecuteScalar(trans, CommandType.Text, ADD_SQL, parms).ToString();

                        id = int.Parse(s);
                        trans.Commit();
                    }
                    catch
                    {
                        trans.Rollback();
                        throw;
                    }
                }
            }

            return id;
        }

        private const string UPDATE_SQL = "update CMS_ARTICLE_VIDEO set VIDEO_NAME=@VIDEO_NAME,VIDEO_FILE_NAME=@VIDEO_FILE_NAME,VIDEO_TYPE=@VIDEO_TYPE,VIDEO_DESC=@VIDEO_DESC,VIDEO_ORDER=@VIDEO_ORDER,STATE=@STATE where VIDEO_ID=@VIDEO_ID";
        //修改基本资料
        public bool Update(CmsArticleVideoModel model)
        {
            SqlParameter[] parms = new SqlParameter[] { 
                new SqlParameter("@VIDEO_NAME", SqlDbType.VarChar,255),
                new SqlParameter("@VIDEO_TYPE",SqlDbType.Int,4),
                new SqlParameter("@VIDEO_FILE_NAME", SqlDbType.VarChar,50),
                new SqlParameter("@VIDEO_DESC", SqlDbType.VarChar,2000),
                new SqlParameter("@VIDEO_ORDER",SqlDbType.Int,4),
                //new SqlParameter("@ARTICLE_ID",SqlDbType.Int,4),
                new SqlParameter("@STATE",SqlDbType.Int,4),
                new SqlParameter("@VIDEO_ID",SqlDbType.Int,4)
            };
            parms[0].Value = model.VideoName;
            parms[1].Value = model.videoType;
            parms[2].Value = model.VideoFileName;
            parms[3].Value = model.videoDesc;
            parms[4].Value = model.VideoOrder;
            //parms[5].Value = model.ArticleId;
            parms[5].Value = model.State;
            parms[6].Value = model.VideoId;
            
            
            int i = SQLHelper.ExecuteSql(UPDATE_SQL, parms);
            if (i > 0) return true;
            return false;
        }
        private const string GET_VIDEO_SQL = "select VIDEO_ID,VIDEO_NAME,VIDEO_FILE_NAME,VIDEO_TYPE,VIDEO_DESC,VIDEO_ORDER,ARTICLE_ID,STATE,ADD_TIME,USER_ID from CMS_ARTICLE_VIDEO where VIDEO_ID=@VIDEO_ID";
        public CmsArticleVideoModel GetVideoModel(int vid)
        {
            CmsArticleVideoModel mim = null;
            SqlParameter[] parms = new SqlParameter[] { 
                new SqlParameter("@VIDEO_ID", SqlDbType.Int, 4)
                
            };
            parms[0].Value = vid;

            using (SqlDataReader rdr = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, GET_VIDEO_SQL, parms))
            {
                if (rdr.Read())
                {
                    mim = new CmsArticleVideoModel();
                    mim.VideoId = rdr.GetInt32(0);
                    mim.VideoName = rdr.GetString(1);
                    mim.VideoFileName = rdr.GetString(2);

                    if (!rdr.IsDBNull(3)) mim.videoType = rdr.GetInt32(3);
                    if (!rdr.IsDBNull(4)) mim.videoDesc = rdr.GetString(4);

                    mim.VideoOrder = rdr.GetInt32(5);
                    mim.ArticleId = rdr.GetInt32(6);
                    mim.State = rdr.GetInt32(7);
                    mim.Add_time = rdr.GetDateTime(8);
                    mim.UserId = rdr.GetInt32(9);
                    
                   
                }
            }
            return mim;
        }

        public IList GetVideoList(CmsArticleVideoModel q)
        {
            string qsql = "select VIDEO_ID,VIDEO_NAME,VIDEO_FILE_NAME,VIDEO_TYPE,VIDEO_DESC,VIDEO_ORDER,ARTICLE_ID,STATE,ADD_TIME,USER_ID from CMS_ARTICLE_VIDEO where 1=1";

            if (q != null)
            {
                if (q.ArticleId > 0 )
                {
                    qsql += " and ARTICLE_ID=" + q.ArticleId;
                }
                if (q.VideoName != null && q.VideoName != "")
                {
                    qsql += " and VIDEO_NAME like '%" + StringUtil.ReplaceSecStr(q.VideoName) + "%'";
                }
            }
            qsql += " order by ADD_TIME desc";
            IList datalist = new ArrayList();
            SqlParameter[] parms = new SqlParameter[] { };

            using (SqlDataReader rdr = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, qsql, parms))
            {
                while (rdr.Read())
                {
                    CmsArticleVideoModel mim = new CmsArticleVideoModel();

                    mim.VideoId = rdr.GetInt32(0);
                    mim.VideoName = rdr.GetString(1);
                    mim.VideoFileName = rdr.GetString(2);

                    mim.videoType = rdr.GetInt32(3);
                    if (!rdr.IsDBNull(4)) mim.videoDesc = rdr.GetString(4);

                    mim.VideoOrder = rdr.GetInt32(5);
                    mim.ArticleId = rdr.GetInt32(6);
                    mim.State = rdr.GetInt32(7);
                    mim.Add_time = rdr.GetDateTime(8);
                    mim.UserId = rdr.GetInt32(9);
                    datalist.Add(mim);

                }
            }
            return datalist;
        }



        public bool Delete(int vid)
        {
            int i = SQLHelper.ExecuteSql("delete from CMS_ARTICLE_VIDEO where VIDEO_ID=" + vid, new SqlParameter[] { });
            if (i > 0) return true;
            return false;
        }

    }
}
